# 公司部门表添加、查询数据

from sys import path
from flask import Blueprint, json, jsonify, request, make_response
from flask_restful import Api, Resource
from xlsxwriter.utility import quote_sheetname
from settings.mysqlM import db
from settings.dataM import wuxian
from settings.fileM import myf
import os
from xlrd import open_workbook
from io import BytesIO
import xlsxwriter
from urllib.parse import quote
import datetime
import mimetypes
from settings.dataM import get_data
from .acl import acl
import datetime, time
from settings.redisM import h

dept = Blueprint('deptdp', __name__)
api = Api(dept)


class Dept(Resource):
    def get(self):
        sql = "select * from depts"
        res = db.find_all(sql)
        res1 = wuxian(res)
        return jsonify({'code': 200, 'list': res1})

    def post(self):
        data = get_data()
        name = data['name']
        pid = data['deptid']
        sql = "insert into depts(name,pid) values('{}',{})".format(name, int(pid))
        db.update(sql)

        return jsonify({'code': 200, 'msg': '添加成功'})

    def delete(self):
        id = request.args.get('id')
        if id:
            sql = "delete from depts where id={}".format(int(id))
            db.update(sql)
            return jsonify({'code': 200, 'msg': '删除成功'})


# 导入
class Daoru(Resource):
    def post(self):
        file = request.files['file']
        newname = myf.rename(file.filename)
        file.save(os.path.join('./static/files/' + newname))
        # 打开文件读取
        name = './static/files/' + newname
        bk = open_workbook(name, encoding_override='utf8')
        sh = bk.sheets()[0]
        nrow = sh.nrows
        ncols = sh.ncols
        for i in range(1, nrow):
            row_date = sh.row_values(i)
            for j in range(0, ncols):
                print(row_date[j])

        file = request.files['file']

        newname = myf.rename(file.filename)
        # 上传文件
        file.save(os.path.join('./static/files/' + newname))
        # 打开文件读取
        # name = './static/files/'+newname
        # name='./static/files/' + '54a79c986aa64036aea62c17adcfdeca.xls'
        bk = open_workbook(name, encoding_override='utf-8')
        sh = bk.sheets()[0]
        nrows = sh.nrows
        ncols = sh.ncols
        for i in range(1, nrows):
            row_date = sh.row_values(i)
            for j in range(0, ncols):
                print(row_date[j])
        return jsonify({'code': 200, 'msg': '成功'})


class Down(Resource):
    def get(self):
        out = BytesIO()
        workbook = xlsxwriter.Workbook(out)
        table = workbook.add_worksheet()
        table.write(0, 0, '班级')
        table.write(0, 1, '讲师')
        workbook.close()
        filename = quote("班级列表_{}.xls".format(
            datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
        file = make_response(out.getvalue())
        out.close()
        mime_type = mimetypes.guess_type('%s.xls' % filename)[0]
        file.headers['Content-Type'] = mime_type
        file.headers["Cache-Control"] = "no-cache"
        file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(
            filename)
        return file


# 获取职务表信息
class PostM(Resource):
    def get(self):
        sql = "select * from posts"
        time.sleep(0.5)
        res = db.find_all(sql)
        return jsonify({'code': 200, 'list': res})

    def post(self):
        data = get_data()
        name = data['name']
        sql = "insert into posts(name) values('{}')".format(name)
        db.update(sql)
        return jsonify({'code': 200, 'msg': '添加成功'})


# 获取岗位
class Gang(Resource):
    def get(self):
        sql = "select * from gang"
        res = db.find_all(sql)
        return jsonify({'code': 200, 'list': res})

    def post(self):
        data = get_data()
        name = data['name']
        sql = "insert into gang(name) values('{}')".format(name)
        db.update(sql)
        return jsonify({'code': 200, 'msg': '添加成功'})


# 模糊查询
class Shows(Resource):
    def get(self):
        name = request.args.get('showname')
        sql = "select * from depts where name like '%{}%'".format(name)
        res = db.find_all(sql)
        return jsonify({'code': 200, 'list': res})


class Resourse(Resource):
    def get(self):
        user_id = request.args.get('userid')
        if user_id:
            # sql="select from user_post as up left join post_resources as pr on up.postid=pr.postid left join resources as r on pr.resourceid=r.id left join resources as ppr on r.id=ppr.pid where up.userid={} and r.type=1".format(int(user_id))
            sql = "select r.id as id, r.name as name,r.url as url,r.id as resourceid,r.pid as pid from user_resources as ur left join resources as r on ur.resourceid=r.id where userid={}".format(
                int(user_id))
            res = db.find_all(sql)
            print(res)
            res1 = wuxian(res)
            ids = []
            for i in res:
                ids.append(i['resourceid'])
            return ({'code': 200, 'list': ids, 'data': res1})
        else:
            sql = "select * from resources"
            res = db.find_all(sql)
            # lst=wuxian(res)
            return jsonify({'code': 200, 'list': res})

    def post(self):
        data = get_data()
        user_id = int(data['userid'])
        ids = data['ids']
        acl.update_resource(user_id, ids)
        return jsonify({"code": 200})


class Addpost(Resource):
    def get(self):
        postid = request.args.get('postid')
        if postid:
            sql = "select resourcesid from  post_resources where postid={}".format(int(postid))

            res = db.find_all(sql)
            ids = []
            if res:
                for i in res:
                    ids.append(i['resourcesid'])
            return ({'code': 200, 'list': ids})
        else:
            time.sleep(0.5)
            sql = "select * from resources"
            res = db.find_all(sql)
            # lst=wuxian(res)
            return jsonify({'code': 200, 'list': res})

    def post(self):
        data = get_data()
        postid = int(data['postid'])
        ids = data['ids']
        if ids:
            sql = "delete from post_resources where postid={}".format(postid)
            db.update(sql)
            for i in ids:
                sql1 = "insert into post_resources(postid,resourcesid) values({},{})".format(postid, int(i))
                db.update(sql1)
            return jsonify({"code": 200})


# 给角色配置资源
class Addrole(Resource):
    def post(self):
        data = get_data()
        ids = data['ids']  # 资源id
        ids1 = data['ids1']  # 继承那些角色的id
        postid = data['postid']  # 角色id
        if ids1:
            sql3 = "update posts set pid='{}' where id={}".format(json.dumps(ids1), int(postid))
            db.update(sql3)

            for i in ids1:
                sql = "select r.id as id from post_resources as pr,resources as r where pr.resourcesid=r.id and pr.postid={}".format(
                    int(i))
                res = db.find_all(sql)
                for j in res:
                    ids.append(j['id'])
        ids = set(ids)
        print(ids)
        sql1 = "delete from post_resources where postid={}".format(int(postid))
        db.update(sql1)
        ids = list(ids)
        print('*' * 30)
        print(ids)
        for a in ids:
            sql2 = "insert into post_resources(postid,resourcesid) values({},{})".format(int(postid), int(a))
            db.update(sql2)
        return 'ok'


class Norole(Resource):
    def post(self):
        data = get_data()
        userid = request.args.get('userid')
        ids = data['ids']
        sql = "select postid as id from user_post where userid={}".format(int(userid))
        res = db.find_all(sql)
        lst = []  # 已有的角色
        lst1 = []  # 互斥的角色
        for i in res:
            lst.append(i['id'])
        for j in lst:
            sql1 = "select roleid2 from roleno where roleid1={}".format(int(j))
            res1 = db.find_all(sql1)
            for a in res1:
                lst1.append(a['roleid2'])
        for y in ids:
            if int(y) not in lst1:
                if int(y) in lst:
                    continue
                sql2 = "insert into user_post(userid,postid) values({},{})".format(int(userid), int(y))
                db.update(sql2)

            continue
        return jsonify({'code': 200, 'msg': '角色添加成功'})


# def outer(fun):
#     def inner(*args,**kargs):
#         now=datetime.datetime.now()
#         key='userid'+'1'
#         params='time'
#         time=h.get_value_hash(key,params)

#         # if now>开始时间 and now<结束时间:
#         #       fun(*args,**kwargs)
#         return inner

# @outer
# # 进行访问


api.add_resource(Dept, '/dept')
api.add_resource(Daoru, '/upload')
api.add_resource(Down, '/down')
api.add_resource(PostM, '/posts')
api.add_resource(Shows, '/shows')
api.add_resource(Gang, '/gang')
api.add_resource(Resourse, '/resourse')
api.add_resource(Addpost, '/addpost')
api.add_resource(Addrole, '/addrole')
api.add_resource(Norole, '/norole')


